﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

//namespace SortDescending
//{
//    class Program
//    {
//        static void Main(string[] args)
//        {
//            // inspired
//            string quote =
//                "Most people have the will to win, few have the will to prepare to win.";

//            // make an array dropping empty items
//            string[] words = quote.Split(new char[] { ' ', ',', '.' }, StringSplitOptions.RemoveEmptyEntries);

//            // sort descending
//            var descending = from word in words orderby word descending select word;

//            foreach (var s in descending)
//                Console.WriteLine(s);

//            Console.ReadLine();
//        }
//    }
//}

//namespace SortDescExtensionMethod
//{
//    class Program
//    {
//        static void Main(string[] args)
//        {
//            // inspired
//            string quote =
//                "Most people have the will to win, few have the will to prepare to win.";

//            // make an array dropping emtpy items
//            string[] words = quote.Split(new char[] { ' ', ',', '.' }, StringSplitOptions.RemoveEmptyEntries);

//            // use extension method directly to sort descending
//            foreach (var s in words.OrderByDescending(s => s))
//                Console.WriteLine(s);

//            Console.ReadLine();
//        }
//    }
//}

//namespace SecondarySort
//{
//    class Program
//    {
//        static void Main(string[] args)
//        {
//            var gamblers = new[]{
//                new { LastName = "Kimmel", First = "Paul", Age = 41 },
//                new { LastName = "Swanson", First = "Dena", Age = 26 },
//                new { LastName = "Swanson", First = "Joe", Age = 4 },
//                new { LastName = "Kimmel", First = "Noah", Age = 1 } };

//            // thenby is implicit in second sort argument
//            var sordid = from gambler in gamblers
//                         orderby gambler.LastName, gambler.Age
//                         select gambler;

//            foreach (var playa in sordid)
//                Console.WriteLine(playa);

//            Console.ReadLine();
//        }
//    }
//}

//namespace SortingDemo
//{
//    class Program
//    {
//        static void Main(string[] args)
//        {
//            // inspired
//            string quote = "Most people have the will to win, few have the will to prepare to win.";

//            // make an array dropping empty items
//            string[] words = quote.Split(new char[] { ' ', ',', '.' }, StringSplitOptions.RemoveEmptyEntries);

//            foreach (var s in words.Reverse())
//                Console.WriteLine(s);

//            Console.ReadLine();
//        }
//    }
//}

//namespace GroupBy
//{
//    class Program
//    {
//        static void Main(string[] args)
//        {
//            var nums = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
//            var result = from n in nums group n by n % 2;
//            // really quite funky
//            Array.ForEach(result.ToArray(), x =>
//            {
//                Console.WriteLine(x.Key == 0 ? "evens:" : "odds:");
//                Array.ForEach(x.ToArray(), y => Console.WriteLine(y));
//            });
//            Console.ReadLine();
//        }
//    }
//}

namespace GroupIntoDemo
{
    class Program
    {
        // set up a typical SQL join
        static string sql = "select Categories.CategoryName, Products.ProductID, " +
            "Products.ProductName, Products.UnitsInStock, Products.UnitPrice " +
            "from Categories INNER JOIN " +
            "Products ON Categories.CategoryID = Products.CategoryID";

        // use the default northwind connection string
        static string connectionString =
            "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True";

        // define a simple class using automatic properties
        public class ProductItem
        {
            public string CategoryName { get; set; }
            public int ProductID { get; set; }
            public string ProductName { get; set; }
            public int UnitsInStock { get; set; }
            public decimal UnitPrice { get; set; }

            public override string ToString()
            {
                const string mask =
                    "Category Name: {0}, " +
                    "Product ID: {1}, " +
                    "Product Name: {2}, " +
                    "Units In Stock: {3}, " +
                    "Unit Price: {4}";

                return string.Format(mask, CategoryName, ProductID,
                    ProductName, UnitsInStock, UnitPrice);
            }
        }

        // a read helper
        static T SafeRead<T>(IDataReader reader, string name, T defaultValue)
        {
            object o = reader[name];
            if (o != System.DBNull.Value && o != null)
                return (T)Convert.ChangeType(o, defaultValue.GetType());

            return defaultValue;
        }

        static void Main(string[] args)
        {
            List<ProductItem> products = new List<ProductItem>();

            // read all of the data into custom objects
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    products.Add(new ProductItem
                    {
                        CategoryName = SafeRead(reader, "CategoryName", ""),
                        ProductID = SafeRead(reader, "ProductID", -1),
                        ProductName = SafeRead(reader, "ProductName", ""),
                        UnitsInStock = SafeRead(reader, "UnitsInStock", 0),
                        UnitPrice = SafeRead(reader, "UnitPrice", 0M)
                    });
                }
            }

            // make sure I have some data
            Array.ForEach(products.ToArray(), y => Console.WriteLine(y));
            Console.ReadLine();

            Console.Clear();
            string line = new string('-', 40);

            // define the LINQ group
            var grouped = from p in products
                          orderby p.CategoryName, p.ProductName
                          group p by p.CategoryName into grp
                          select new { Category = grp.Key, Product = grp };

            // dump each group
            Array.ForEach(grouped.ToArray(), g =>
                {
                    Console.WriteLine(g.Category);
                    Console.WriteLine(line);
                    // dump each product in the group
                    Array.ForEach(g.Product.ToArray(), p =>
                        Console.WriteLine(p));
                    Console.WriteLine(Environment.NewLine);
                });

            Console.ReadLine();
        }

    }
}